package ru.reinvent.perspectiva.cash;

import java.util.ArrayList;
import java.util.HashMap;

import org.joda.time.DateTime;

import com.google.common.collect.Sets;

import android.content.ContentProvider;
import android.content.ContentProviderOperation;
import android.content.ContentProviderResult;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.OperationApplicationException;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.os.RemoteException;
import android.text.TextUtils;

public class PerspectivaProvider extends ContentProvider 
	{
	
	// content provider constants stuff:
	public static final String PROVIDER_NAME = "ru.reinvent.perspectiva.cash.PerspectivaProvider";
	private static final String VENDORS_URI_STRING = "content://" + PROVIDER_NAME + "/vendors";
	private static final String LOTS_URI_STRING = "content://" + PROVIDER_NAME + "/lots";
	private static final String _ID = "_id";
	
	private static final String VENDORS_NAME = "vendors_name";
	
	private static final String LOTS_VENDOR_ID = "vendor_id";
	private static final String LOTS_NAME = "name";
	private static final String LOTS_LINK = "link";
	private static final String LOTS_ADD_DATE = "add_date";
	private static final String LOTS_PRICE = "price";
	private static final String LOTS_HULL = "hull";
	private static final String LOTS_REFERENCE = "reference";
	private static final String LOTS_GENDER = "gender";
	private static final String LOTS_TYPE = "type";
	private static final String LOTS_DESC = "description";
	private static final String LOTS_STATE = "state";
	private static final String LOTS_REGION = "region";
	private static final String LOTS_IMG_URL = "img";
	private static final String LOTS_IMG_CASH_URI = "img_cash";
	
	
	// additional variables for easy using through switch-case construction 
	private static final int MANY_VENDORS = 1;
	private static final int ONE_VENDOR = 2;
	private static final int MANY_LOTS = 3;
	private static final int ONE_LOT = 4;

	private static final UriMatcher uriMatcher;
	static {
		uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
		uriMatcher.addURI(PROVIDER_NAME, "vendors", MANY_VENDORS);
		uriMatcher.addURI(PROVIDER_NAME, "vendors/#", ONE_VENDOR);
		
		uriMatcher.addURI(PROVIDER_NAME, "lots", MANY_LOTS);
		uriMatcher.addURI(PROVIDER_NAME, "lots/#", ONE_LOT);

	}

	//---- DB Stuff ----//
	private SQLiteDatabase cashDB;
	private static final String DATABASE_NAME = "Perspectiva";
	private static final int DATABASE_VERSION = 2;

	private static final String VENDORS_TABLE = "vendors";
	private static final String VENDORS_TABLE_CREATE =
		"CREATE TABLE IF NOT EXISTS " + VENDORS_TABLE + " ("
		+ _ID +				" INTEGER PRIMARY KEY AUTOINCREMENT, "
		+ VENDORS_NAME +	" TEXT not null "
		+ ");";
	
	private static final String LOTS_TABLE = "lots";
	private static final String LOTS_TABLE_CREATE =
		"CREATE TABLE IF NOT EXISTS " + LOTS_TABLE + " ("
		+ _ID +					" INTEGER PRIMARY KEY AUTOINCREMENT, "
		+ LOTS_VENDOR_ID +		" INTEGER not null, "
		+ LOTS_NAME +			" TEXT not null, "
		+ LOTS_LINK +			" TEXT not null, "
		+ LOTS_ADD_DATE +		" INTEGER not null, "
		+ LOTS_PRICE +			" TEXT not null, "
		+ LOTS_HULL +			" TEXT not null, "
		+ LOTS_REFERENCE +		" TEXT not null, "
		+ LOTS_GENDER +			" TEXT not null, "
		+ LOTS_TYPE +			" TEXT not null, "
		+ LOTS_DESC +			" TEXT not null, "
		+ LOTS_STATE +			" TEXT not null, "
		+ LOTS_REGION +			" TEXT not null, "
		+ LOTS_IMG_URL +		" TEXT not null, "
		+ LOTS_IMG_CASH_URI +	" TEXT not null "
		+ ");";

	private static class PerspectivaDBH extends SQLiteOpenHelper {

		PerspectivaDBH(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(VENDORS_TABLE_CREATE);
			db.execSQL(LOTS_TABLE_CREATE);
		}

		@Override
		public void onOpen(SQLiteDatabase db) {
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + VENDORS_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + LOTS_TABLE);
			onCreate(db);
		}
	}
	//---- DB Stuff ----//
	
	
	@Override
	public String getType(Uri uri) {
		switch (uriMatcher.match(uri)) {
			case MANY_VENDORS: return "vnd.android.cursor.dir/vnd.vendors";
			case ONE_VENDOR: return "vnd.android.cursor.item/vnd.vendors";
			
			case MANY_LOTS: return "vnd.android.cursor.dir/vnd.lots";
			case ONE_LOT: return "vnd.android.cursor.item/vnd.lots";
			
			
			default: throw new IllegalArgumentException("I know nothing about this URI: " + uri);
		}
	}

	@Override
	public boolean onCreate() {
		Context context = getContext();
		PerspectivaDBH dbHelper = new PerspectivaDBH(context);
		cashDB = dbHelper.getWritableDatabase();
		return (cashDB == null) ? false : true;
	}

	@Override
	public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
		SQLiteQueryBuilder sqlBuilder = new SQLiteQueryBuilder();
		String table = chooseTable(uri);
		sqlBuilder.setTables( table );

		if (uriMatcher.match(uri) == ONE_VENDOR) sqlBuilder.appendWhere(_ID + " = " + uri.getPathSegments().get(1));
		if (uriMatcher.match(uri) == ONE_LOT) sqlBuilder.appendWhere(_ID + " = " + uri.getPathSegments().get(1));  

		if (sortOrder == null) 
			{
			if (table == VENDORS_TABLE) sortOrder = _ID + " ASC";
			else sortOrder = _ID + " ASC";
			}
		
		Cursor c = sqlBuilder.query(cashDB, projection, selection, escapeArgs(selectionArgs), null, null, sortOrder);
		c.setNotificationUri(getContext().getContentResolver(), uri);
		return c;
	}

	@Override
	public Uri insert(Uri uri, ContentValues values) {
		long rowID = cashDB.insert( chooseTable(uri), "", values );

		// if added succesfully
		if (rowID > 0) {
			Uri _uri = ContentUris.withAppendedId(uri, rowID);
			getContext().getContentResolver().notifyChange(_uri, null);
			return _uri;
		}
		throw new SQLException("Failed to insert row into " + uri);
	}

	@Override
	public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
		int count = 0;
		String table = chooseTable(uri);
		switch (uriMatcher.match(uri)) {
			case MANY_VENDORS:
			case MANY_LOTS:
				count = cashDB.update(table, values, selection, escapeArgs(selectionArgs));
				break;
			case ONE_VENDOR:
				count = cashDB.update(table, values,
						_ID + " = " + uri.getPathSegments().get(1) + (!TextUtils.isEmpty(selection) ? " AND (" + selection + ')' : ""),
						escapeArgs(selectionArgs));
				break;
			case ONE_LOT:
				count = cashDB.update(table, values,
						_ID + " = " + uri.getPathSegments().get(1) + (!TextUtils.isEmpty(selection) ? " AND (" + selection + ')' : ""),
						escapeArgs(selectionArgs));
				break;
			default:
				throw new IllegalArgumentException("Unknown URI " + uri);
		}
		getContext().getContentResolver().notifyChange(uri, null);
		return count;
	}

	@Override
	public int delete(Uri uri, String selection, String[] selectionArgs) {
		int count = 0;
		String table = chooseTable(uri);
		switch (uriMatcher.match(uri)) {
			case MANY_LOTS:
			case MANY_VENDORS:
				count = cashDB.delete(table, selection, escapeArgs(selectionArgs));
				break;
			case ONE_VENDOR:
				count = cashDB.delete(table,
						_ID + " = " + uri.getPathSegments().get(1) + (!TextUtils.isEmpty(selection) ? " AND (" + selection + ')' : ""),
						escapeArgs(selectionArgs));
				break;
			case ONE_LOT:
				count = cashDB.delete(table,
						_ID + " = " + uri.getPathSegments().get(1) + (!TextUtils.isEmpty(selection) ? " AND (" + selection + ')' : ""),
						escapeArgs(selectionArgs));
				break;
			default:
				throw new IllegalArgumentException("Unknown URI " + uri);
		}
		getContext().getContentResolver().notifyChange(uri, null);
		return count;
	}

	private String chooseTable(Uri uri) {
		String table = VENDORS_TABLE;
		switch(uriMatcher.match(uri)) {
			case MANY_VENDORS:
			case ONE_VENDOR:
				table = VENDORS_TABLE;
				break;
			case MANY_LOTS:
			case ONE_LOT:
				table = LOTS_TABLE;
				break;
			default:
				throw new IllegalArgumentException("I know nothing about this URI: " + uri);
		}
		return table;
	}

	private String[] escapeArgs(String[] args)
		{
		if (args == null) return null;
		
		String[] escArgs = new String[args.length];
		for (int i=0; i<args.length; i++) escArgs[i] = args[i].replace("'","''").replace("\"","'\"");
		
		return escArgs; 
		}
	
	public static final class V {
		public static final String URI_STRING = PerspectivaProvider.VENDORS_URI_STRING;
		public static final Uri CONTENT_URI = Uri.parse( PerspectivaProvider.VENDORS_URI_STRING );

		public static final String _ID = PerspectivaProvider._ID;
		public static final String NAME = PerspectivaProvider.VENDORS_NAME;
	}

	public static final class L {
		public static final String URI_STRING = PerspectivaProvider.LOTS_URI_STRING;
		public static final Uri CONTENT_URI = Uri.parse( PerspectivaProvider.LOTS_URI_STRING );

		public static final String _ID = PerspectivaProvider._ID;
		public static final String VENDOR_ID = PerspectivaProvider.LOTS_VENDOR_ID;
		public static final String NAME = PerspectivaProvider.LOTS_NAME;
		public static final String LINK = PerspectivaProvider.LOTS_LINK;
		public static final String ADD_DATE = PerspectivaProvider.LOTS_ADD_DATE;
		public static final String PRICE = PerspectivaProvider.LOTS_PRICE;
		public static final String HULL = PerspectivaProvider.LOTS_HULL;
		public static final String REFERENCE = PerspectivaProvider.LOTS_REFERENCE;
		public static final String GENDER = PerspectivaProvider.LOTS_GENDER;
		public static final String TYPE = PerspectivaProvider.LOTS_TYPE;
		public static final String DESCRIPTION = PerspectivaProvider.LOTS_DESC;
		public static final String STATE = PerspectivaProvider.LOTS_STATE;
		public static final String REGION = PerspectivaProvider.LOTS_REGION;
		public static final String IMG_URL = PerspectivaProvider.LOTS_IMG_URL;
		public static final String IMG_CASH_URI = PerspectivaProvider.LOTS_IMG_CASH_URI;
	}

	
	public static final class DBUsage 
		{
		public static boolean clearDB(Context ctx)
			{
			ctx.getContentResolver().delete(V.CONTENT_URI,null,null);
			ctx.getContentResolver().delete(L.CONTENT_URI,null,null);

			return isVendorsEmpty(ctx) && isLotsEmpty(ctx);
			}
		
		public static Vendor createVendor(Cursor c)
			{
			Vendor vnd = new Vendor();
			vnd.setId(c.getInt(c.getColumnIndex(V._ID)));
			vnd.setName(c.getString(c.getColumnIndex(V.NAME))==null ? "" : c.getString(c.getColumnIndex(V.NAME)));	
			
			return vnd;
			}
		
		public static Vendor cascadeCreateVendor(Cursor c, Context ctx)
			{
			Vendor vnd = createVendor(c);
		
			Cursor lots = ctx.getContentResolver().query(L.CONTENT_URI,null,L.VENDOR_ID+"="+vnd.getId(),null,null);
			if (lots.getCount() != 0)
				{
				lots.moveToFirst();
				while(lots.moveToNext()) vnd.addLot(createLot(lots));
				}
			
			lots.close();
			return vnd;
			}
		
		public static Lot createLot(Cursor c)
			{
			Lot lot = new Lot();
			lot.setId(c.getInt(c.getColumnIndex(L._ID)));
			lot.setVendorId(c.getInt(c.getColumnIndex(L.VENDOR_ID)));
			lot.setName(c.getString(c.getColumnIndex(L.NAME))==null ? "" : c.getString(c.getColumnIndex(L.NAME)));
			lot.setLink(c.getString(c.getColumnIndex(L.LINK))==null ? "" : c.getString(c.getColumnIndex(L.LINK)));
			lot.setAddDate(c.getLong(c.getColumnIndex(L.ADD_DATE)));
			lot.setPrice(c.getString(c.getColumnIndex(L.PRICE))==null ? "" : c.getString(c.getColumnIndex(L.PRICE)));
			lot.setHull(c.getString(c.getColumnIndex(L.HULL))==null ? "" : c.getString(c.getColumnIndex(L.HULL)));
			lot.setReference(c.getString(c.getColumnIndex(L.REFERENCE))==null ? "" : c.getString(c.getColumnIndex(L.REFERENCE)));
			lot.setGender(c.getString(c.getColumnIndex(L.GENDER))==null ? "" : c.getString(c.getColumnIndex(L.GENDER)));
			lot.setType(c.getString(c.getColumnIndex(L.TYPE))==null ? "" : c.getString(c.getColumnIndex(L.TYPE)));
			lot.setDescription(c.getString(c.getColumnIndex(L.DESCRIPTION))==null ? "" : c.getString(c.getColumnIndex(L.DESCRIPTION)));
			lot.setState(c.getString(c.getColumnIndex(L.STATE))==null ? "" : c.getString(c.getColumnIndex(L.STATE)));
			lot.setRegion(c.getString(c.getColumnIndex(L.REGION))==null ? "" : c.getString(c.getColumnIndex(L.REGION)));
			lot.setImageUrl(c.getString(c.getColumnIndex(L.IMG_URL)));
			lot.setImageCashUri(c.getString(c.getColumnIndex(L.IMG_CASH_URI)));
			
			return lot;
			}

		public static boolean isLotsEmpty(Context ctx) 
			{
			String[] proj = { L._ID };
			Cursor c = ctx.getContentResolver().query(L.CONTENT_URI,proj,null,null,null);
			
			if (c.getCount() == 0) { c.close(); return true; }
			else { c.close(); return false; }
			}
		
		public static boolean isVendorsEmpty(Context ctx) 
			{
			String[] proj = { V._ID };
			Cursor c = ctx.getContentResolver().query(V.CONTENT_URI,proj,null,null,null);
		
			if (c.getCount() == 0) { c.close(); return true; }
			else { c.close(); return false; }
			}

		public static Vendor getVendorById(int id, Context ctx)
			{
			Cursor c = ctx.getContentResolver().query(V.CONTENT_URI,null,V._ID+"="+id,null,null);
			
			Vendor vnd = null;
			if (c.getCount() != 0) 
				{
				c.moveToFirst();
				vnd = createVendor(c); 
				}
			
			c.close(); 
			return vnd;
			}

		public static Vendor getFullVendorById(int id, Context ctx)
			{
			Cursor c = ctx.getContentResolver().query(V.CONTENT_URI,null,V._ID+"="+id,null,null);
			
			Vendor vnd = null;
			if (c.getCount() != 0) 
				{
				c.moveToFirst();
				vnd = cascadeCreateVendor(c,ctx); 
				}
			
			c.close(); 
			return vnd;
			}

		public static Vendor getVendorByName(String name, Context ctx)
			{
			String[] args = { name };
			Cursor c = ctx.getContentResolver().query(V.CONTENT_URI,null,V.NAME+"=?",args,null);
			
			Vendor vnd = null;
			if (c.getCount() != 0) 
				{
				c.moveToFirst();
				vnd = createVendor(c); 
				}
			
			c.close(); 
			return vnd;
			}

		public static Vendor getFullVendorByName(String name, Context ctx)
			{
			String[] args = { name };
			Cursor c = ctx.getContentResolver().query(V.CONTENT_URI,null,V.NAME+"=?",args,null);
			
			Vendor vnd = null;
			if (c.getCount() != 0) 
				{
				c.moveToFirst();
				vnd = cascadeCreateVendor(c,ctx); 
				}
			
			c.close(); 
			return vnd;
			}

		public static Lot getLotById(int id, Context ctx)
			{
			Cursor c = ctx.getContentResolver().query(L.CONTENT_URI,null,L._ID+"="+id,null,null);
			
			Lot lot = null;
			if (c.getCount() != 0) 
				{
				c.moveToFirst();
				lot = createLot(c); 
				}
			
			c.close(); 
			return lot;
			}

		public static Lot getLotByRefAndVndId(int id, String ref, Context ctx)
			{
			String[] args = { String.valueOf(id), ref };
			Cursor c = ctx.getContentResolver().query(L.CONTENT_URI,null,L.VENDOR_ID+"=? AND "+L.REFERENCE+"=?",args,null);
			
			Lot lot = null;
			if (c.getCount() != 0) 
				{
				c.moveToFirst();
				lot = createLot(c); 
				}
			
			c.close(); 
			return lot;
			}

		public static boolean isLotInDB(int id, Context ctx)
			{
			String[] proj = { L._ID };
			Cursor c = ctx.getContentResolver().query(L.CONTENT_URI,proj,L._ID+"="+id,null,null);
			
			if (c.getCount() == 0) { c.close(); return false; }
			else { c.close(); return true; }
			}

		public static boolean isVendorInDB(int id, Context ctx)
			{
			String[] proj = { V._ID };
			Cursor c = ctx.getContentResolver().query(V.CONTENT_URI,proj,V._ID+"="+id,null,null);
			
			if (c.getCount() == 0) { c.close(); return false; }
			else { c.close(); return true; }
			}
		
		private static ContentValues createLotContentValues(Lot lot)
			{
			ContentValues val = new ContentValues();
			val.put(L.VENDOR_ID,lot.getVendorId());
			val.put(L.NAME,lot.getName()==null ? "" : lot.getName());
			val.put(L.LINK,lot.getLink()==null ? "" : lot.getLink());
			val.put(L.ADD_DATE,lot.getAddDate()==null ? (new DateTime()).getMillis() : lot.getAddDate().getMillis());
			val.put(L.PRICE,lot.getPrice()==null ? "" : lot.getPrice());
			val.put(L.HULL,lot.getHull()==null ? "" : lot.getHull());
			val.put(L.REFERENCE,lot.getReference()==null ? "" : lot.getReference());
			val.put(L.GENDER,lot.getGender()==null ? "" : lot.getGender());
			val.put(L.TYPE,lot.getType()==null ? "" : lot.getType());
			val.put(L.DESCRIPTION,lot.getDescription()==null ? "" : lot.getDescription());
			val.put(L.STATE,lot.getState()==null ? "" :lot.getState());
			val.put(L.REGION,lot.getRegion()==null ? "" : lot.getRegion());
			val.put(L.IMG_URL,lot.getImageUrl()==null ? "" : lot.getImageUrl());
			val.put(L.IMG_CASH_URI,lot.getImageCashUri()==null ? "" : lot.getImageCashUri());
			
			return val;
			}
		
		public static Lot addLotIfNotInDB(Lot lot, Context ctx)
			{
			Lot testLot = getLotByRefAndVndId(lot.getVendorId(),lot.getReference(),ctx);
			if (testLot != null) return testLot;


			Uri res = ctx.getContentResolver().insert(L.CONTENT_URI, createLotContentValues(lot));
			if (res != null) { lot.setId((int) ContentUris.parseId(res)); return lot; }
			else return null;
			}
		
		public static Vendor addVendorIfNotInDB(Vendor vnd, Context ctx)
			{
			Vendor testVnd = getVendorByName(vnd.getName(),ctx);
			if (testVnd != null) { vnd.setId(testVnd.getId()); return vnd; }
			
			ContentValues val = new ContentValues();
			val.put(V.NAME,vnd.getName());

			Uri res = ctx.getContentResolver().insert(V.CONTENT_URI, val);
			if (res != null) { vnd.setId((int) ContentUris.parseId(res)); return vnd; }
			else return null;
			}
		
		public static Vendor cascadeAddVendorIfNotInDB(Vendor vnd, Context ctx)
			{
			Vendor addedVnd = addVendorIfNotInDB(vnd,ctx);
			if (addedVnd == null) return null;
			vnd.setId(addedVnd.getId());
			
			ArrayList<Lot> lots = vnd.getLots();
			vnd.setLots(new ArrayList<Lot>());
			
			Lot addedLot = null;
			for (int i=0; i<lots.size(); i++)
				{
				lots.get(i).setVendorId(vnd.getId());

				addedLot = addLotIfNotInDB(lots.get(i),ctx);
				if (addedLot == null) return null;
				
				vnd.addLot(addedLot);
				}
			
			return vnd;
			}
		
		public static int[] mergeCash(ArrayList<Vendor> vnds, Context ctx) throws RemoteException, OperationApplicationException
			{
			int addedVendors = 0;
			int removedVendors = 0;
			
			final HashMap<String,ArrayList<Lot>> vndsMap = new HashMap<String,ArrayList<Lot>>();
			for (int i=0; i<vnds.size(); i++)
				{
				if (vndsMap.containsKey(vnds.get(i).getName()))
					vndsMap.get(vnds.get(i).getName()).addAll(vnds.get(i).getLots());
				else vndsMap.put(vnds.get(i).getName(),vnds.get(i).getLots());
				}
			
			final HashMap<String,Integer> vndsDB = new HashMap<String,Integer>();
			final Cursor vndsCursor = ctx.getContentResolver().query(V.CONTENT_URI,null,null,null,null);
			while (vndsCursor.moveToNext()) 
				{
				final String vndName = vndsCursor.getString(vndsCursor.getColumnIndex(V.NAME)); 
				final int vndId = vndsCursor.getInt(vndsCursor.getColumnIndex(V._ID)); 
				vndsDB.put(vndName, vndId);
				}
			vndsCursor.close();
			
			final ArrayList<ContentProviderOperation> vndAddOperations = new ArrayList<ContentProviderOperation>();
			for (String addVndName : Sets.difference(vndsMap.keySet(),vndsDB.keySet()))
				{
				final ContentProviderOperation addVnd = ContentProviderOperation.newInsert(V.CONTENT_URI).
						withValue(V.NAME,addVndName).
						build();
				vndAddOperations.add(addVnd);
				addedVendors++;
				}
			ContentProviderResult[] addRes = ctx.getContentResolver().applyBatch(PROVIDER_NAME, vndAddOperations);
			int i = 0;
			for (String addVndName : Sets.difference(vndsMap.keySet(),vndsDB.keySet())) 
				{
				vndsDB.put(addVndName, (int) ContentUris.parseId(addRes[i].uri));
				i++;
				}
			
			final ArrayList<ContentProviderOperation> vndRemoveOperations = new ArrayList<ContentProviderOperation>();
			for (String removeVndName : Sets.difference(vndsDB.keySet(),vndsMap.keySet()).immutableCopy())
				{
				final ContentProviderOperation removeVnd = ContentProviderOperation.newDelete(V.CONTENT_URI).
						withSelection(V._ID+"="+vndsDB.get(removeVndName),null).
						build();
				final ContentProviderOperation removeLots = ContentProviderOperation.newDelete(L.CONTENT_URI).
						withSelection(L.VENDOR_ID+"="+vndsDB.get(removeVndName),null).
						build();
				vndRemoveOperations.add(removeVnd);
				vndRemoveOperations.add(removeLots);
				vndsDB.remove(removeVndName);
				removedVendors++;
				}
			ctx.getContentResolver().applyBatch(PROVIDER_NAME, vndRemoveOperations);
			
			int addedLots = 0;
			int removedLots = 0;
			final ArrayList<ContentProviderOperation> lotsOperations = new ArrayList<ContentProviderOperation>();
			
			final ArrayList<Lot> lotsDB = new ArrayList<Lot>();
			final String[] projection = { L._ID, L.VENDOR_ID, L.NAME, L.REFERENCE };
			final Cursor lotsCursor = ctx.getContentResolver().query(L.CONTENT_URI,projection,null,null,null);
			while (lotsCursor.moveToNext()) 
				{
				final Lot lot = new Lot();
				lot.setId(lotsCursor.getInt(lotsCursor.getColumnIndex(L._ID)));
				lot.setVendorId(lotsCursor.getInt(lotsCursor.getColumnIndex(L.VENDOR_ID)));
				lot.setName(lotsCursor.getString(lotsCursor.getColumnIndex(L.NAME)));
				lot.setReference(lotsCursor.getString(lotsCursor.getColumnIndex(L.REFERENCE)));
				
				lotsDB.add(lot);
				}
			lotsCursor.close();
			
			for (String vendor : vndsMap.keySet())
				{
				final ArrayList<Lot> lots = vndsMap.get(vendor);
				final ArrayList<Lot> vndLotsDB = new ArrayList<Lot>();
				for (int j=0; j<lotsDB.size(); j++) if (lotsDB.get(j).getVendorId() == vndsDB.get(vendor)) vndLotsDB.add(lotsDB.get(j));
				
				for (int j=0; j<lots.size(); j++)
					{
					boolean hasInDB = false;
					for (int k=0; k<vndLotsDB.size(); k++) 
						{
						if (lots.get(j).getName().equals(vndLotsDB.get(k).getName()) &&
							lots.get(j).getReference().equals(vndLotsDB.get(k).getReference()))
							{
							hasInDB = true;
							break;
							}
						}
					
					if (!hasInDB)
						{
						lots.get(j).setVendorId(vndsDB.get(vendor));
						final ContentProviderOperation addLot = ContentProviderOperation.newInsert(L.CONTENT_URI).
								withValues(createLotContentValues(lots.get(j))).
								build();
						lotsOperations.add(addLot);
						addedLots++;
						}
					}

				for (int j=0; j<vndLotsDB.size(); j++)
					{
					boolean removeFromDB = true;
					for (int k=0; k<lots.size(); k++) 
						{
						if (vndLotsDB.get(j).getName().equals(lots.get(k).getName()) &&
							vndLotsDB.get(j).getReference().equals(lots.get(k).getReference()))
							{
							removeFromDB = false;
							break;
							}
						}
					
					if (removeFromDB)
						{
						final ContentProviderOperation removeLot = ContentProviderOperation.newDelete(L.CONTENT_URI).
								withSelection(L._ID+"="+vndLotsDB.get(j).getId(),null).
								build();
						lotsOperations.add(removeLot);
						removedLots++;
						}
					}
				}
			ctx.getContentResolver().applyBatch(PROVIDER_NAME, lotsOperations);
			
			return new int[] { addedVendors, removedVendors, addedLots, removedLots };
			}
		}
		
}
